﻿﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Quexcel.OpenXML.Exceptions;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO.Packaging;
using Quexcel.OpenXML.Excel.Workbook;
using Quexcel.OpenXML.Transaction;
using Quexcel.OpenXML.Interfaces;
using Quexcel.OpenXML.Excel.Worksheet;
using Quexcel.OpenXML.Excel.Worksheet.IO;
using Quexcel.OpenXML.Excel;
using System.Collections;
using Quexcel.OpenXML.Excel.Worksheet.Components;

namespace Quexcel.OpenXML
{
    public class ExcelCommand
    {
        /// <summary>
        /// Command to be executed. 
        /// </summary>
        public string CommandText { get; set; }
        /// <summary>
        /// Specifies the Relaxed/Strict approach of execution. (Opimistic is the default behavior)
        /// Relaxed approach will try to execute the statement with warnings even in case of errors. 
        ///     Like it will insert in the default sheet even if its unable to find the data.
        /// Strict approach will block any erroneous statement and will promt an error for that.
        /// </summary>
        public CommandExecutionMode ExecutionMode { get; set; }

        private ExcelConnection connection;
        private ExcelTransaction transaction;
        private bool CommitTransactionManually;

        #region Ctor
        public ExcelCommand(ExcelConnection connection, string commandText, CommandExecutionMode warningLevel)
            : this(new ExcelTransaction(connection), commandText, warningLevel)
        {
            CommitTransactionManually = true;
        }
        public ExcelCommand(ExcelConnection connection, string commandText) : this(connection, null, CommandExecutionMode.Relaxed) { }
        public ExcelCommand(ExcelConnection connection) : this(connection, null) { }

        public ExcelCommand(ExcelTransaction transaction, string commandText, CommandExecutionMode warningLevel)
        {
            this.ExecutionMode = warningLevel;
            this.connection = transaction.connection;
            this.transaction = transaction;
            this.CommandText = commandText;
        }
        public ExcelCommand(ExcelTransaction transaction, string commandText) : this(transaction, commandText, CommandExecutionMode.Relaxed) { }
        public ExcelCommand(ExcelTransaction transaction) : this(transaction, null) { }
        #endregion

        internal void ExecuteNonQuery()
        {
            if (String.IsNullOrEmpty(CommandText))
                throw new InvalidCommandException(CommandText, "Command Text not specified.");
            else
            {
                QueryParser parser = QueryParser.GetInstance();
                QueryElements queryElements = parser.ParseQuery(CommandText);

                if (queryElements.Type == QueryType.InsertIntoTable)
                {
                    Range range = connection.workbook.GetRange(queryElements.TableName, transaction);
                    ExecuteInsertIntoTableQuery(range, queryElements);
                }
                else if (queryElements.Type == QueryType.InsertIntoSheet)
                {
                    Worksheet worksheet = connection.workbook.GetSheet(queryElements.TableName, transaction);
                    ExecuteInsertIntoSheetQuery(worksheet, queryElements);
                }
            }
        }

        internal IEnumerator<Row> ExecuteReader()
        {
            if (String.IsNullOrEmpty(CommandText))
                throw new InvalidCommandException(CommandText, "Command Text not specified.");
            else
            {
                QueryParser parser = QueryParser.GetInstance();
                QueryElements queryElements = parser.ParseQuery(CommandText);
                if (queryElements.Type == QueryType.SelectFromSheet)
                {
                    Worksheet worksheet = connection.workbook.GetSheet(queryElements.TableName, transaction);
                    return ExecuteSheetReader(worksheet, queryElements);
                }
                else if (queryElements.Type == QueryType.SelectFromTable)
                {
                    Range range = connection.workbook.GetRange(queryElements.TableName, transaction);
                    return ExecuteTableReader(range, queryElements);
                }
            }

            return null;
        }

        private void ExecuteInsertIntoSheetQuery(Worksheet worksheet, QueryElements queryElements)
        {
            Dictionary<string, string> headerRow = worksheet.ReadHeader();
            string[] insertValues = queryElements.InsertValues.Split(',');
            string[] insertAddress = new string[insertValues.Length];

            int cnt = 0;
            bool isDataInserted = true;
            foreach (string header in queryElements.InsertOrder.Split(','))
            {
                Address cellAddress = Utils.Util.GetAddress(headerRow[header.Trim()]);
                insertAddress[cnt] = cellAddress.ToString();
                cnt++;
            }
            isDataInserted = worksheet.AppendRow(insertValues, insertAddress);

            if (CommitTransactionManually)
            {
                if (isDataInserted)
                    transaction.Commit();
                transaction.Dispose();
            }
        }

        private void ExecuteInsertIntoTableQuery(Range range, QueryElements queryElements)
        {
            Dictionary<string, string> headerRow = range.ReadHeader();
            string[] insertValues = queryElements.InsertValues.Split(',');
            string[] insertAddress = new string[insertValues.Length];

            int cnt = 0;
            bool isDataInserted = true;
            foreach (string header in queryElements.InsertOrder.Split(','))
            {
                Address cellAddress = Utils.Util.GetAddress(headerRow[header.Trim()]);
                insertAddress[cnt] = cellAddress.ToString();
                cnt++;
            }
            isDataInserted = range.AppendRow(insertValues, insertAddress);

            if (CommitTransactionManually)
            {
                if (isDataInserted)
                    transaction.Commit();
                transaction.Dispose();
            }
        }

        private IEnumerator<Row> ExecuteSheetReader(Worksheet worksheet, QueryElements queryElements)
        {
            return worksheet.Select();
        }

        private IEnumerator<Row> ExecuteTableReader(Range range, QueryElements queryElements)
        {
            return range.Select();
        }

        private void ExecuteCreateQuery(ExecuteQueryParameters parameters)
        {
            /*if (parameters.worksheetDoc != null)
                throw new InvalidCommandException(CommandText, "A table with the same name already exists in the database.");
            else
            {
                if (connection.sheetPart != null)
                {
                    parameters.worksheetDoc = transaction.Open(connection.sheetPart);

                    string sheetName = (new Regex("[^/]+(?<sheetName>\\s+).xml")).Match(connection.sheetPart.Uri.ToString()).Result("${sheetName}");
                    parameters.range = new Range(connection.workbookDoc, connection.nsManager, sheetName, sheetName + "!$" + Range.parseCellNumber(1) + "$1", RangeType.Sheet);
                }
                else
                    throw new InvalidCommandException(CommandText, "Cannot locate the database to create the table. Specify the Initial Catalog in the ConnectionString");
            }*/
        }

        private void ExecuteDeleteQuery(ExecuteQueryParameters parameters)
        {

        }

        private void ExecuteAlterQuery(ExecuteQueryParameters parameters)
        {

        }

        private void ExecuteUpdateQuery(ExecuteQueryParameters parameters)
        {

        }
    }

    public enum CommandExecutionMode
    {
        Relaxed,
        Strict
    }

    struct ExecuteQueryParameters
    {
        public XmlDocument workbookDoc;
        public XmlDocument worksheetDoc;
        public Range range;
        public XmlDocument sharedStringDoc;
        public QueryElements queryElements;
        public int minRowIndex;
        public int minCellIndex;
        public int maxCellIndex;
        public int maxRowIndex;
    }
}
